{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:48:36.493658Z",
     "start_time": "2024-04-24T09:48:16.462739Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from mlxtend.preprocessing import TransactionEncoder\n",
    "from mlxtend.frequent_patterns import apriori, association_rules\n",
    "import configparser  #读取配置文件标准库\n",
    "from sqlalchemy import create_engine\n",
    "# 数据库配置\n",
    "config = configparser.ConfigParser()\n",
    "config.read(\"..\\\\..\\\\..\\\\资源配置\\\\数据库配置\\\\config.ini\")\n",
    "db_config = {\n",
    "    'user': config['mysql-db4free-database']['USER'],\n",
    "    'password': config['mysql-db4free-database']['PASSWORD'],\n",
    "    'host': config['mysql-db4free-database']['HOST'],\n",
    "    'port': config['mysql-db4free-database']['PORT'],\n",
    "    'database': config['mysql-db4free-database']['DBNAME']\n",
    "}\n",
    "#定义数据库连接\n",
    "engine = create_engine(\n",
    "    f\"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 名词解释\n",
    "## 指标解释\n",
    "confidence==>置信度:置信度（A -> B）= (同时包含物品A和B的记录数)   /  (包含A的记录数)\n",
    "lift==>提升度:不常用,简单理解就是>1就比较准\n",
    "support==>支持度:（包含物品A的记录数量） / （总的记录数量）  得到了物品A的支持度\n",
    "## 代码名词\n",
    "## 假设我们有一个包含用户购买记录的数据集，其中每一行代表一个用户的购物商品数组"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "814ace8d1d4e84b1"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "member_id\n11    [商品015, 商品0130, 商品0112, 商品0114, 商品0136, 商品012,...\n12    [商品0124, 商品0112, 商品0136, 商品0138, 商品0125, 商品014...\n13    [商品0119, 商品0130, 商品0139, 商品0128, 商品0127, 商品012...\n14    [商品0118, 商品0126, 商品015, 商品012, 商品0134, 商品0115,...\n15    [商品0122, 商品0125, 商品016, 商品0121, 商品0110, 商品0130...\nName: item_name, dtype: object"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "sql_query = \"SELECT distinct  member_id,item_name FROM recommend_orders limit 3000\"\n",
    "sqldf = pd.read_sql_query(sql_query, engine )\n",
    "sqldfData = sqldf.groupby('member_id')['item_name'].agg(list)\n",
    "sqldfData.head()\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:48:47.679804Z",
     "start_time": "2024-04-24T09:48:36.497682Z"
    }
   },
   "id": "e4cabc56582f62c2",
   "execution_count": 2
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 将数据转换为TransactionEncoder对象，便于处理"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "5cc3053e1637643f"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   商品011  商品0110  商品0111  商品0112  商品0113  商品0114  商品0115  商品0116  商品0117  \\\n0   True    True    True    True    True    True    True    True   False   \n1   True   False    True    True    True   False    True    True    True   \n2   True    True    True    True    True    True   False    True    True   \n3   True    True    True    True    True    True    True   False    True   \n4   True    True    True    True    True    True   False   False    True   \n\n   商品0118  ...  商品0137  商品0138  商品0139  商品014  商品0140  商品015  商品016  商品017  \\\n0   False  ...    True   False    True   True    True   True   True   True   \n1    True  ...    True    True    True  False    True  False  False   True   \n2   False  ...    True    True    True   True    True   True   True   True   \n3    True  ...    True    True    True   True    True   True  False   True   \n4    True  ...    True   False    True   True   False   True   True   True   \n\n   商品018  商品019  \n0   True   True  \n1   True   True  \n2  False  False  \n3   True   True  \n4   True  False  \n\n[5 rows x 40 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>商品011</th>\n      <th>商品0110</th>\n      <th>商品0111</th>\n      <th>商品0112</th>\n      <th>商品0113</th>\n      <th>商品0114</th>\n      <th>商品0115</th>\n      <th>商品0116</th>\n      <th>商品0117</th>\n      <th>商品0118</th>\n      <th>...</th>\n      <th>商品0137</th>\n      <th>商品0138</th>\n      <th>商品0139</th>\n      <th>商品014</th>\n      <th>商品0140</th>\n      <th>商品015</th>\n      <th>商品016</th>\n      <th>商品017</th>\n      <th>商品018</th>\n      <th>商品019</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>False</td>\n      <td>...</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>...</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>...</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>...</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>...</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>True</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 40 columns</p>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "te = TransactionEncoder()\n",
    "te_ary = te.fit(sqldfData).transform(sqldfData)\n",
    "df = pd.DataFrame(te_ary, columns=te.columns_)\n",
    "df.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:48:47.841763Z",
     "start_time": "2024-04-24T09:48:47.691904Z"
    }
   },
   "id": "41ea029df7c569ee",
   "execution_count": 3
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 应用APRIORI算法找到频繁项集(每一个数据在所有行重出现的次数/总次数),输出频繁项集"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "c9febc0350aab759"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Frequent Itemsets:\n",
      "       support                                itemsets\n",
      "0     0.855556                                 (商品011)\n",
      "1     0.866667                                (商品0110)\n",
      "2     0.855556                                (商品0111)\n",
      "3     0.866667                                (商品0112)\n",
      "4     0.833333                                (商品0113)\n",
      "...        ...                                     ...\n",
      "4846  0.611111           (商品017, 商品0135, 商品016, 商品014)\n",
      "4847  0.600000           (商品017, 商品015, 商品0135, 商品016)\n",
      "4848  0.600000           (商品017, 商品016, 商品0136, 商品014)\n",
      "4849  0.600000            (商品017, 商品015, 商品016, 商品014)\n",
      "4850  0.600000  (商品0114, 商品016, 商品0134, 商品014, 商品0110)\n",
      "\n",
      "[4851 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# 应用APRIORI算法找到频繁项集,这里人工指定以下最小支持度\n",
    "# 支持度解释:（包含物品A的记录数量） / （总的记录数量）  得到了物品A的支持度\n",
    "frequent_itemsets = apriori(df, min_support=0.6, use_colnames=True)\n",
    "# 输出频繁项集\n",
    "print(\"Frequent Itemsets:\")\n",
    "print(frequent_itemsets)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:48:48.100186Z",
     "start_time": "2024-04-24T09:48:47.857974Z"
    }
   },
   "id": "18f445a1401cef37",
   "execution_count": 4
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 计算关联规则，设置最小置信度阈值为0.6"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "1d352c37ca01a6ee"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# 置信度解释:置信度（A -> B）= (同时包含物品A和B的记录数)   /  (包含A的记录数)\n",
    "association_rules = association_rules(frequent_itemsets, metric=\"lift\", min_threshold=0.6)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:48:48.508780Z",
     "start_time": "2024-04-24T09:48:48.102610Z"
    }
   },
   "id": "9dd4b2ef83765a0e",
   "execution_count": 5
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 输出关联规则"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "518e6d99f50e32fd"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " Association Rules:\n"
     ]
    },
    {
     "data": {
      "text/plain": "  antecedents consequents  antecedent support  consequent support   support  \\\n0    (商品0110)     (商品011)            0.866667            0.855556  0.733333   \n1     (商品011)    (商品0110)            0.855556            0.866667  0.733333   \n2     (商品011)    (商品0111)            0.855556            0.855556  0.733333   \n3    (商品0111)     (商品011)            0.855556            0.855556  0.733333   \n4    (商品0112)     (商品011)            0.866667            0.855556  0.755556   \n\n   confidence      lift  leverage  conviction  zhangs_metric  \n0    0.846154  0.989011 -0.008148    0.938889      -0.076923  \n1    0.857143  0.989011 -0.008148    0.933333      -0.071429  \n2    0.857143  1.001855  0.001358    1.011111       0.012821  \n3    0.857143  1.001855  0.001358    1.011111       0.012821  \n4    0.871795  1.018981  0.014074    1.126667       0.139706  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>antecedents</th>\n      <th>consequents</th>\n      <th>antecedent support</th>\n      <th>consequent support</th>\n      <th>support</th>\n      <th>confidence</th>\n      <th>lift</th>\n      <th>leverage</th>\n      <th>conviction</th>\n      <th>zhangs_metric</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>(商品0110)</td>\n      <td>(商品011)</td>\n      <td>0.866667</td>\n      <td>0.855556</td>\n      <td>0.733333</td>\n      <td>0.846154</td>\n      <td>0.989011</td>\n      <td>-0.008148</td>\n      <td>0.938889</td>\n      <td>-0.076923</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>(商品011)</td>\n      <td>(商品0110)</td>\n      <td>0.855556</td>\n      <td>0.866667</td>\n      <td>0.733333</td>\n      <td>0.857143</td>\n      <td>0.989011</td>\n      <td>-0.008148</td>\n      <td>0.933333</td>\n      <td>-0.071429</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>(商品011)</td>\n      <td>(商品0111)</td>\n      <td>0.855556</td>\n      <td>0.855556</td>\n      <td>0.733333</td>\n      <td>0.857143</td>\n      <td>1.001855</td>\n      <td>0.001358</td>\n      <td>1.011111</td>\n      <td>0.012821</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>(商品0111)</td>\n      <td>(商品011)</td>\n      <td>0.855556</td>\n      <td>0.855556</td>\n      <td>0.733333</td>\n      <td>0.857143</td>\n      <td>1.001855</td>\n      <td>0.001358</td>\n      <td>1.011111</td>\n      <td>0.012821</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>(商品0112)</td>\n      <td>(商品011)</td>\n      <td>0.866667</td>\n      <td>0.855556</td>\n      <td>0.755556</td>\n      <td>0.871795</td>\n      <td>1.018981</td>\n      <td>0.014074</td>\n      <td>1.126667</td>\n      <td>0.139706</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\n Association Rules:\")\n",
    "association_rules.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:51:14.717154Z",
     "start_time": "2024-04-24T09:51:14.672134Z"
    }
   },
   "id": "2155ea40974d568f",
   "execution_count": 7
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 定义推荐函数，根据用户已购商品返回推荐商品"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "42dc3e2fdfe9409"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "def get_recommendations(user_basket, rules):\n",
    "    user_basket_set = set(user_basket)\n",
    "    recommendations = []\n",
    "\n",
    "    for _, rule in rules.iterrows():\n",
    "        antecedents = rule['antecedents']\n",
    "        consequents = rule['consequents']\n",
    "\n",
    "        if antecedents.issubset(user_basket_set) and rule['confidence'] >= 0.6:\n",
    "            recommendations.extend(consequents)\n",
    "\n",
    "    return list(set(recommendations))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:51:23.182047Z",
     "start_time": "2024-04-24T09:51:23.171459Z"
    }
   },
   "id": "529e65ec748ca277",
   "execution_count": 8
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 示例：给已购买\"商品015\"和\"商品0117\"的用户推荐商品"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "21f700513a37404b"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Recommendations for user with basket ['商品015', '商品0117']:\n",
      "['商品0138', '商品015', '商品019', '商品0119', '商品0115', '商品0117', '商品018', '商品0133', '商品0110', '商品0125', '商品0136', '商品0116', '商品0135', '商品0123', '商品0111', '商品0140', '商品0126', '商品0128', '商品0139', '商品0120', '商品0112', '商品0127', '商品0134', '商品0137', '商品0121', '商品014', '商品0118', '商品017', '商品0129', '商品0113', '商品0114', '商品0132', '商品016', '商品013', '商品0130', '商品011', '商品0124', '商品0131', '商品0122', '商品012']\n"
     ]
    }
   ],
   "source": [
    "user_basket = ['商品015', '商品0117']\n",
    "recommendations = get_recommendations(user_basket, association_rules)\n",
    "print(\"\\nRecommendations for user with basket ['商品015', '商品0117']:\")\n",
    "print(recommendations)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-24T09:51:42.941808Z",
     "start_time": "2024-04-24T09:51:38.526526Z"
    }
   },
   "id": "e641d907639f723e",
   "execution_count": 9
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "7a83a6c49bcc59e2",
   "execution_count": null
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
